package com.hy.dao.impl;
/*
 * @ Date: 2024/5/14 22:33
 * @ Description: OrderstableDaoImpl
 **/

import com.hy.bean.Orderstable;
import com.hy.dao.OrderstableDao;
import com.hy.dto.OrdersDto;
import com.hy.dto.OrdersPartsDto;
import com.hy.exception.BizException;
import com.hy.utils.DBManager;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.hy.vo.OrdersPartstableVo;
import com.hy.vo.OrderstableVo;
import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.*;
import org.apache.commons.lang3.StringUtils;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.text.SimpleDateFormat;
import java.util.*;

public class OrderstableDaoImpl implements OrderstableDao {
    private QueryRunner qr = new QueryRunner();
    private SimpleDateFormat sdf = new SimpleDateFormat();
    private SimpleDateFormat sdf02 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    BasicRowProcessor basicRowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());
    @Override
    public List<Map<String, Object>> queryById(Integer id) {
        Connection connection = null;
        try {
            connection = DBManager.getConnection();
            String sql = " select o.*,c.clientName from orderstable o\n" +
                    "left join clienttable c on o.client_id = c.id where o.id = ?";
            List<Map<String,Object>> mapList = qr.query(connection,sql,new MapListHandler(),id);
            return mapList;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
    }
    @Override
    public List<Orderstable> queryAllByLimit() {
        return null;
    }
    @Override
    public List<Map<String, Object>> queryAll(String status,Integer page,Integer limit) {
        Connection connection = null;
        try {
            connection = DBManager.getConnection();
            String sql = "select o.*,c.clientName,e.employeeName creator,e1.employeeName lastModifier from orderstable o \n" +
                    "left join clienttable c on o.client_id = c.id\n" +
                    "left join employeetable e on o.creator = e.id\n" +
                    "left join employeetable e1 on o.lastUpdatePerson = e1.id where audit_status = ? limit ?,?";
            List<Map<String,Object>> mapList = qr.query(connection,sql,new MapListHandler(),status,(page - 1) * limit, limit);
            return mapList;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
    }
    @Override
    public Long count(String status) {
        Connection connection = null;
        try {
            connection = DBManager.getConnection();
            String sql = "select count(*) from orderstable o \n" +
                    "left join clienttable c on o.client_id = c.id \n" +
                    "left join employeetable e on o.creator = e.id \n" +
                    "left join employeetable e1 on o.lastUpdatePerson = e1.id where audit_status = ?";
            Long query = qr.query(connection, sql, new ScalarHandler<>(),status);

            return query;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
    }
    //已完成
    @Override
    public List<Map<String, Object>> queryAll2(String status,Integer page,Integer limit) {
        Connection connection = null;
        try {
            connection = DBManager.getConnection();
            String sql = "select o.*,c.clientName,e.employeeName creator,e1.employeeName lastModifier from orderstable o \n" +
                    "left join clienttable c on o.client_id = c.id\n" +
                    "left join employeetable e on o.creator = e.id\n" +
                    "left join employeetable e1 on o.lastUpdatePerson = e1.id where okstatus = ? limit ?,?";
            List<Map<String,Object>> mapList = qr.query(connection,sql,new MapListHandler(),status,(page - 1) * limit, limit);
            return mapList;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
    }
    //已完成
    @Override
    public Long count2(String status) {
        Connection connection = null;
        try {
            connection = DBManager.getConnection();
            String sql = "select count(*) from orderstable o \n" +
                    "left join clienttable c on o.client_id = c.id \n" +
                    "left join employeetable e on o.creator = e.id \n" +
                    "left join employeetable e1 on o.lastUpdatePerson = e1.id where okstatus = ?";
            Long query = qr.query(connection, sql, new ScalarHandler<>(),status);

            return query;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
    }
    @Override
    public List<Orderstable> selectAll() {
        Connection connection = null;
        String sql = "select * from orderstable where audit_status = 1 and okstatus = 0";
        try {
            connection = DBManager.getConnection();
            return qr.query(connection, sql, new BeanListHandler<>(Orderstable.class));
        } catch (Exception e) {
            throw new BizException("Dao异常",e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    @Override
    public OrderstableVo selectOrdersParts(Integer id) {
        Connection connection = null;
        String sql = "select o.*,c.id cid, c.clientName,emp.employeeName from orderstable o join clientTable c on o.client_id = c.id join employeetable emp on o.creator = emp.id where o.id = ?";
        String sql2 = "select op.*,car.partName,car.partStore,car.costPrice from orders_partstable op join carpartstable car on op.partsTable_id = car.id  where op.ordersTable_id = ?";
        try {
            connection = DBManager.getConnection();
            OrderstableVo query = qr.query(connection, sql, new BeanHandler<>(OrderstableVo.class,basicRowProcessor),id);
            query.setChild(qr.query(connection, sql2, new BeanListHandler<>(OrdersPartstableVo.class,basicRowProcessor),query.getId()));
            return query;
        } catch (Exception e) {
            throw new BizException("Dao异常",e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    @Override
    public List<Map<String, Object>> queryAllStatus(String status,Integer page,Integer limit) {
        Connection connection = null;
        try {
            connection = DBManager.getConnection();
            String sql = "select o.*,c.clientName,e.employeeName creator,e1.employeeName lastModifier from orderstable o \n" +
                    "left join clienttable c on o.client_id = c.id\n" +
                    "left join employeetable e on o.creator = e.id\n" +
                    "left join employeetable e1 on o.lastUpdatePerson = e1.id where audit_status != ? limit ?,?";
            List<Map<String,Object>> mapList = qr.query(connection,sql,new MapListHandler(),status,(page - 1) * limit, limit);
            return mapList;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            try {
                DbUtils.close(connection);
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }
    }
    @Override
    public Long countStatus(String status) {
        Connection connection = null;
        try {
            connection = DBManager.getConnection();
            String sql = "select count(*) from orderstable o \n" +
                    "left join clienttable c on o.client_id = c.id \n" +
                    "left join employeetable e on o.creator = e.id \n" +
                    "left join employeetable e1 on o.lastUpdatePerson = e1.id where audit_status != ?";
            return qr.query(connection, sql, new ScalarHandler<>(), status);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            try {
                DbUtils.close(connection);
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        }
    }
    @Override
    public List<Map<String, Object>> select(String orderId, String beginDate, String endDate, String creator){
        Connection conn = null;
        try {
            conn = DBManager.getConnection();
        } catch (Exception e) {
            throw new BizException("建立连接出错啦",e);
        }
        StringBuilder sb = new StringBuilder("select o.*,c.clientName clientname , e.employeeName employeename " +
                " from orderstable o " +
                " inner join clienttable c on c.id = o.client_id " +
                " inner join employeetable e on e.id = o.creator ");
        List<Object> paramList = new ArrayList<>();
        boolean isWhere = true;

        if (!StringUtils.isBlank(orderId)) {
            if (isWhere){
                sb.append(" where ");
                isWhere = false;
            } else {
                sb.append(" or ");
            }

            sb.append("orderId like ? ");
            paramList.add("%"+orderId+"%");
        }

        if (!StringUtils.isBlank(beginDate) && !StringUtils.isBlank(endDate)) {
            if (isWhere){
                sb.append(" where ");
                isWhere = false;
            } else {
                sb.append(" or ");
            }
            sb.append(" (createTime >= ? and createTime<= ?)");

            paramList.add(beginDate);
            paramList.add(endDate);

        }else if(!StringUtils.isBlank(beginDate) && StringUtils.isBlank(endDate)){
            if (isWhere){
                sb.append(" where ");
                isWhere = false;
            } else {
                sb.append(" or ");
            }

            sb.append(" (createTime >= ? )");
            paramList.add(beginDate);
        } else if(!StringUtils.isBlank(beginDate) && StringUtils.isBlank(endDate)){
            if (isWhere){
                sb.append(" where ");
                isWhere = false;
            } else {
                sb.append(" or ");
            }

            sb.append(" (partCreateDate<= ?) ");
            paramList.add(endDate);
        }

        if (!StringUtils.isBlank(creator)){
            if (isWhere){
                sb.append(" where ");
                isWhere = false;
            } else {
                sb.append(" or ");
            }
            sb.append(" e.employeeName like ? ");
            paramList.add("%"+creator+"%");
        }

        String sql = sb.toString();

        List<Map<String, Object>> mapList = null ;
        try{
            if (paramList.size() >=1 )
                mapList =  qr.query(conn, sql, new MapListHandler(), paramList.toArray());
            else
                mapList = qr.query(conn,sql, new MapListHandler());
        }catch (Exception e){
            throw new BizException("查询出错啦",e);
        }finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        return mapList;
    }

    @Override
    public List<Map<String, Object>> select(){
        Connection conn = null;
        try {
            conn = DBManager.getConnection();
            String sql = "select o.*,c.clientName clientname,e.employeeName employeename from orderstable o inner join clienttable c on c.id = o.client_id inner join employeetable e on e.id = o.creator ";
            List<Map<String, Object>> mapList = qr.query(conn, sql, new MapListHandler());

            return mapList;
        } catch (Exception e) {
            throw new BizException("Dao出错啦",e);
        }finally {
            try {
                DbUtils.close(conn);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }

        }

    }
    @Override
    public int updateOkstatusById(Connection connection,Integer id,Integer okstatus){
        String sql = "update orderstable set okstatus = ? where id = ?";
        try {
            return qr.update(connection, sql, okstatus,id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    @Override
    public int updateIsHaveById(Connection connection, Integer id, Integer isHave) {
        String sql = "update orderstable set isHave = ? where id = ?";
        try {
            return qr.update(connection, sql, isHave,id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public boolean audit(Integer id, Integer lastUpdatePerson, String status) {
        Connection connection = null;

        try {
            connection = DBManager.getConnection();
            String sql = "update orderstable set lastUpdatePerson = ?,audit_status = ?,lastUpdateTime = ? where id = ?";
            int count = qr.update(connection, sql, lastUpdatePerson,status,sdf02.format(new Date()),id);
            return count > 0;
        } catch (Exception e) {
            try {
                DbUtils.close(connection);
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        }
    }

    @Override
    public int insert(OrdersDto ordersDto, Connection connection) {
        String sql ="insert into orderstable(id,orderId,client_id,creator,createtime) values(null,?,?,?,?)";

        try {
            BigInteger integer = qr.insert(connection, sql,new ScalarHandler<>(), ordersDto.getOrderid(), ordersDto.getClientId(), ordersDto.getCreator(), ordersDto.getCreatetime());

            return integer.intValue();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    @Override
    public int update(OrdersPartsDto orderstable, Connection connection) {
        String sql ="update orderstable set lastUpdatePerson = ?,lastUpdateTime = ? where id = ?";

        try {
            int update = qr.update(connection, sql, orderstable.getLastUpdatePerson(), sdf02.format(new Date()), orderstable.getId());

            return update;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    @Override
    public int deleteById(Integer id) {
        return 0;
    }

}
